package com.example.tossednfound;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Handles User interactions with Database
 * Checks if User has trusted access 
 */
public class DatabaseHandler {

	//User Table
	public static final String PK_USERID = 		"user_id";
	public static final String KEY_EMAIL = 		"user_email";
	public static final String KEY_PASSWORD = 	"user_password";
	public static final String KEY_ISLOCKED = 	"user_isLocked";
	public static final String KEY_ISADMIN = 	"user_isAdmin";
	public static final String KEY_ADDRESS = 	"user_address";
	public static final String KEY_STATE = 		"user_state";
	public static final String KEY_ZIPCODE = 	"user_zipcode";
	public static final String KEY_COUNTRY = 	"user_country";
	public static final String KEY_CREATEDDATE_USER = "user_createdDate";
	public static final String KEY_NAME_USER = 	"user_name";

	//Item Table
	public static final String PK_ITEMID = "item_id";
	public static final String KEY_CREATOR = "item_creator";
	public static final String KEY_PHOTO = "item_photo";
	public static final String KEY_REWARDAMOUNT = "item_rewardAmount";
	public static final String KEY_CATEGORY = "item_category";
	public static final String KEY_LOCATION = "item_location";
	public static final String KEY_TAGS = "item_tags";
	public static final String KEY_ISDONATION = "item_isDonation";
	public static final String KEY_CREATEDDATE_ITEM = "item_createdDate";
	public static final String KEY_NAME_ITEM = "item_name";
	public static final String KEY_ISLOST = "item_isLost";

	//Database
	private static final String DATABASE_NAME = "TossedNFoundDB";
	private static final String DATABASE_USERTABLE = "userTable";
	private static final String DATABASE_ITEMTABLE = "itemTable";
	private static final int DATABASE_VERSION = 4;

	private DBHelper helper;
	private final Context context;
	private SQLiteDatabase database;

	private static class DBHelper extends SQLiteOpenHelper {

		public DBHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		/**
		 * Creates item table with appropriate fields and columns 
		 *
		 * @param db Database
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			//Creates User Table
			db.execSQL("CREATE TABLE " + DATABASE_USERTABLE + " (" +
					PK_USERID + 	" INTEGER PRIMARY KEY AUTOINCREMENT, " + 
					KEY_EMAIL + 	" TEXT NOT NULL UNIQUE, " +
					KEY_PASSWORD + 	" TEXT NOT NULL, " +
					KEY_ISLOCKED + 	" INTEGER NOT NULL, " +
					KEY_ISADMIN + 	" INTEGER NOT NULL, " +
					KEY_ADDRESS + 	" TEXT, " +
					KEY_STATE + 	" TEXT, " +
					KEY_ZIPCODE + 	" INTEGER, " +
					KEY_COUNTRY + 	" TEXT, "  +
					KEY_NAME_USER + " TEXT, " +
					KEY_CREATEDDATE_USER + " TEXT);"
					);
			//Creates Item Table //FIX DIS
			db.execSQL("CREATE TABLE " + DATABASE_ITEMTABLE + " (" +
					PK_ITEMID + 		" INTEGER PRIMARY KEY AUTOINCREMENT, " + 
					KEY_CREATOR + 		" TEXT NOT NULL, " +
					KEY_PHOTO + 		" BLOB, " +
					KEY_REWARDAMOUNT + 	" REAL DEFAULT 0, " +
					KEY_ISDONATION + 	" INTEGER NOT NULL DEFAULT 0, " +
					KEY_ISLOST + 		" INTEGER NOT NULL DEFAULT 0, " +
					KEY_CATEGORY + 		" TEXT, " +
					KEY_LOCATION + 		" TEXT, " +
					KEY_TAGS + 			" TEXT, " +
					KEY_CREATEDDATE_ITEM + " DATE NOT NULL, " +
					KEY_NAME_ITEM + 	" TEXT NOT NULL);"
					);
			
					

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_USERTABLE + ";");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_ITEMTABLE + ";");
			onCreate(db);
		}

	}
	
	public DatabaseHandler(Context context) {
		this.context = context;
	}
	
	/**
	 * Makes DB readable
	 */
	public DatabaseHandler openReadable() throws SQLiteException{
		helper = new DBHelper(context);
		database = helper.getReadableDatabase();
		return this;
	}

	/**
	 * Makes DB writeable
	 */
	public DatabaseHandler openWriteable() throws SQLiteException{
		helper = new DBHelper(context);
		database = helper.getReadableDatabase();
		return this;
	}

	public void close() {
		helper.close();
	}

	/**
	 * Checks if inputted username matches corresponding password
	 * 
	 * @param email Inputed username
	 * @param password
	 */
	public boolean checkLogin(String email, String password) {
		String[] columns = {KEY_EMAIL ,KEY_PASSWORD};
		String selection = KEY_EMAIL + " = " + email + " AND " + KEY_PASSWORD + " = " + password;
		Cursor ret = database.query(DATABASE_USERTABLE, columns, selection, null, null, null, null);
		return ret.getCount()==1; //if there is a user with these credentials, return true
	}
	
	/**
	 * Does not allow new user to create account with e-mail that already exists in DB
	 *
	 * @param email 
	 */
	public boolean emailIsAvailable(String email) {
		String[] columns = {KEY_EMAIL};
		String selection = KEY_EMAIL + " = " + email;
		Cursor ret = database.query(DATABASE_USERTABLE, columns, selection, null, null, null, null);
		return ret.getCount()==0; //if there is a user with this email, return false
	}

	/**
	 * Checks if user is an AdminUser
	 *
	 * @param email Admin's e-mail
	 */
	public boolean isAdmin(String email) {
		String[] columns = {KEY_EMAIL};
		String selection = KEY_EMAIL + " = " + email + " AND " + KEY_ISADMIN + " = 1";
		Cursor ret = database.query(DATABASE_USERTABLE, columns, selection, null, null, null, null);
		return ret.getCount()==1; //if there is a column, then the user is an admin
	}
	
	/**
	 * Creates a new user with all fields in form inputed
	 * 
	 * @param email
	 * @param password
	 * @param isAdmin
	 * @param address
	 * @param state
	 * @param zipcode
	 * @param country
	 * @param name
	 */
	public long createUser(String email, String password, boolean isAdmin, 
			String address, String state, int zipcode, String country, String name) {
		ContentValues cv = new ContentValues();
		int val = isAdmin? 1 : 0; //converting isAdmin to a boolean value
		cv.put(KEY_EMAIL, email);
		cv.put(KEY_PASSWORD, password);      
		cv.put(KEY_ISLOCKED, 0); //defaulted to false;           
		cv.put(KEY_ISADMIN, val);             
		cv.put(KEY_ADDRESS, address);
		cv.put(KEY_STATE, state);
		cv.put(KEY_ZIPCODE, zipcode);
		cv.put(KEY_COUNTRY, country);                   
		cv.put(KEY_CREATEDDATE_USER, ""); //TODO:figure out how to put in current date in format (long?)
		cv.put(KEY_NAME_USER, name);            
		return database.insert(DATABASE_USERTABLE, null, cv);	
	}
	
	/**
	 * Creates new user with only email and password inputed
	 * 
	 * @param email
	 * @param password
	 */
	public long simpleCreateUser(String email, String password) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_EMAIL, email);
		cv.put(KEY_PASSWORD, password);      
		cv.put(KEY_ISLOCKED, 0); //defaulted to false;           
		cv.put(KEY_ISADMIN, 0);             
		cv.put(KEY_ADDRESS, "Address");
		cv.put(KEY_STATE, "State");
		cv.put(KEY_ZIPCODE, 32309);
		cv.put(KEY_COUNTRY, "US");                   
		cv.put(KEY_CREATEDDATE_USER, "01/01/2013"); //TODO:figure out how to put in current date in format (long?)
		cv.put(KEY_NAME_USER, "Name");   
		return database.insert(DATABASE_USERTABLE, null, cv);
	}
	
	/**
	 * Creates new entry for lost item in item DB
	 *
	 */
	public long createLostEntry() {
		//TODO: Implement this method
		return 0;
	}
	
	/**
	 * Creates new entry for a found item in item DB
	 *
	 */
	public long createFoundEntry() {
		//TODO: Implement this method
		return 0;
	}
	
	/**
	 * Removes entry(found or lost item) from item DB
	 *
	 */
	public boolean removeEntry() {
		//TODO: Implement this method
		return false;
	}
	
	/**
	 * Removes user from DB
	 *
	 */
	public boolean removeUser() {
		//TODO: Implement this method
		return false;
	}

}
